﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace WINBASE.Utils.DBHelper
{
    public class TableHandler
    {
        QueryHelper dbHandler = new QueryHelper();

        #region Constructor

        public TableHandler() { }

        public TableHandler(SqlConnection Connection) { }

        public TableHandler(SqlConnection Connection, string TableName)
        {
            dbHandler.Connection = this.Connection;
        }

        #endregion

        #region Property

        private SqlConnection _Connection = null;
        public SqlConnection Connection
        {
            get { return _Connection; }
            set
            {
                _Connection = value;
                dbHandler.Connection = _Connection;
            }
        }

        private string _TableName = "";
        public string TableName
        {
            get { return _TableName; }
            set { _TableName = value; }
        }

        #endregion

        //public object getDetail(string IdentitiyColumnName,int Id,out object DAL)
        //{
        //    object[] myDetail = dbHandler.GetARow("SELECT * FROM "+ TableName +" WHERE "+ IdentitiyColumnName +" = @s", Id);

        //    //return new WardDAL(Convert.ToInt32(myWard[0]), Convert.ToString(myWard[1]),
        //    //                   Convert.ToString(myWard[2]), Convert.ToInt32(myWard[3]),
        //    //                   Convert.ToInt32(myWard[4]));
        //}

        public int SaveTable(string TableName, object DAL)
        {
            Dictionary<string, object> myRow = new Dictionary<string, object>();
            foreach (PropertyInfo myProperty in DAL.GetType().GetProperties())
            {
                myRow[myProperty.Name] = myProperty.GetValue(DAL, null);
            }

            string str1 = "(", str2 = " VALUES(";
            List<object> rangeargs = new List<object>();
            for (int i = 0; i < myRow.Count; i++)
            {
                str1 += myRow.Keys.ToList()[i] + (i == myRow.Count - 1 ? ")" : ",");
                str2 += "@s" + (i == myRow.Count - 1 ? ")" : ",");
                rangeargs.Add(myRow.Values.ToList()[i]);
            }
            string CommandText = "INSERT INTO " + TableName + str1 + str2 + "\n" + "SELECT @SCOPE_IDENTITY";
            return (int)dbHandler.GetAVal(CommandText, rangeargs);
        }


        public bool IsSaveValid(string ColumnName, object Value)
        {
            int count = (int)dbHandler.GetAVal(" SELECT COUNT(*) FROM [" + _TableName + "] WHERE (" + ColumnName + " = @s) ", Value);
            return count > 0 ? false : true;
        }

        public bool IsUpdateValid(string ColumnName, object Value, string IdentityColumnName, int Id)
        {
            int count = (int)dbHandler.GetAVal(" SELECT COUNT(*) FROM [" + _TableName + "] WHERE (" + ColumnName + " = @s) And (" + IdentityColumnName + " != @s) ", Value, Id);
            return count > 0 ? false : true;
        }

        public bool IsDeleteValid(string[] TableNames, string ColumnName, object Value)
        {
            int count = 0;
            foreach (string TableName in TableNames)
            {
                count += (int)dbHandler.GetAVal("SELECT COUNT(*) FROM " + TableName + " WHERE " + ColumnName + " = @s", Value);
            }
            return count > 0 ? false : true;
        }
    }
}
